/* Create Table Space */

/* 1.TBL_USER */
CREATE TABLESPACE TS_TBL_USER DATAFILE 'D:/oracle/product/10.2.0/oradata/orcl/TS_TBL_USER.dbf' SIZE 4M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
/* 2.TBL_GROUP */
CREATE TABLESPACE TS_TBL_GROUP DATAFILE 'D:/oracle/product/10.2.0/oradata/orcl/TS_TBL_GROUP.dbf' SIZE 4M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
/* 3.TBL_CSRLOG */
CREATE TABLESPACE TS_TBL_CSRLOG DATAFILE 'D:/oracle/product/10.2.0/oradata/orcl/TS_TBL_CSRLOG.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
/* 4.TBL_PARAMETERS */
CREATE TABLESPACE TS_TBL_PARAMETERS DATAFILE 'D:/oracle/product/10.2.0/oradata/orcl/TS_TBL_PARAMETERS.dbf' SIZE 5M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
/* 5. TBL_GROUP_ACCOUNT */
CREATE TABLESPACE TS_TBL_GROUP_ACCOUNT DATAFILE 'D:/oracle/product/10.2.0/oradata/orcl/TS_TBL_GROUP_ACCOUNT.dbf' SIZE 4M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
/* 6. TBL_MODULE */
CREATE TABLESPACE TS_TBL_MODULE DATAFILE 'D:/oracle/product/10.2.0/oradata/orcl/TS_TBL_MODULE.dbf' SIZE 4M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
/* 7. TBL_PERMISSION */
CREATE TABLESPACE TS_TBL_PERMISSION DATAFILE 'D:/oracle/product/10.2.0/oradata/orcl/TS_TBL_PERMISSION.dbf' SIZE 4M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

/* Start Drop */
DROP TABLE BGADMIN.TBL_USER CASCADE CONSTRAINT;
DROP INDEX BGADMIN.IX1_TBL_USER;
drop sequence TBL_USER_SEQ;
drop trigger TBL_USER_TRIGGER;

DROP TABLE BGADMIN.TBL_GROUP CASCADE CONSTRAINT;
DROP INDEX BGADMIN.IX1_TBL_GROUP;
drop sequence TBL_GROUP_SEQ;
drop trigger TBL_GROUP_TRIGGER;

DROP TABLE TBL_CSRLOG CASCADE CONSTRAINT; 
drop sequence TBL_CSRLOG_SEQ;
drop trigger TBL_CSRLOG_TRIGGER;

DROP TABLE TBL_PARAMETERS CASCADE CONSTRAINT;

DROP TABLE BGADMIN.TBL_GROUP_ACCOUNT CASCADE CONSTRAINT;
drop trigger TBL_GROUP_ACCOUNT_TRIGGER;

DROP INDEX BGADMIN.IX1_TBL_GROUP_ACCOUNT;
drop sequence TBL_GROUP_ACCOUNT_SEQ;

DROP TABLE TBL_MODULE CASCADE CONSTRAINT;

DROP TABLE TBL_PERMISSION CASCADE CONSTRAINT;
/* End Drop */

/* 1.TBL_USER */
CREATE TABLE BGADMIN.TBL_USER
(
    USER_ID 		NUMBER(19,0) NOT NULL, 
	EMAIL   		VARCHAR2(255) NOT NULL UNIQUE,
	FULL_NAME   		VARCHAR2(255),
	STATUS  		VARCHAR2(10),
	PASSWORD 		VARCHAR2(255), 
	LOCKED          VARCHAR2(5),
	ENABLED         VARCHAR2(5),
	DATEPWCHANGED    TIMESTAMP (6),
	DATELASTACCESSED TIMESTAMP (6),
	GROUP_CODE		VARCHAR2 (50) NOT NULL,
	CREATED_BY 		VARCHAR2 (50), 
	CREATED_DATE 	TIMESTAMP (6),
	UPDATED_BY 		VARCHAR2 (50), 
	UPDATED_DATE 	TIMESTAMP (6), 
	CONSTRAINT PK_TBL_USER PRIMARY KEY (USER_ID)
)
TABLESPACE TS_TBL_USER;

CREATE INDEX BGADMIN.IX1_TBL_USER ON BGADMIN.TBL_USER (USER_ID, EMAIL) 
TABLESPACE TS_TBL_USER;

create sequence TBL_USER_SEQ start with 1 increment by 1 nomaxvalue;

/* 2.TBL_GROUP */
CREATE TABLE BGADMIN.TBL_GROUP (
  GROUP_ID 			NUMBER(19,0) NOT NULL, 
  GROUP_NAME		VARCHAR2(200) NOT NULL,
  GROUP_CODE		VARCHAR2(50) NOT NULL UNIQUE,
  PARENT_CODE 		VARCHAR2(50),
  CREATED_BY 		VARCHAR2 (50), 
  CREATED_DATE 		TIMESTAMP (6),
  UPDATED_BY 		VARCHAR2 (50), 
  UPDATED_DATE 		TIMESTAMP (6), 
  CONSTRAINT PK_TS_TBL_GROUP PRIMARY KEY (GROUP_ID)
)
TABLESPACE TS_TBL_GROUP;

CREATE INDEX BGADMIN.IX1_TBL_GROUP ON BGADMIN.TBL_GROUP (GROUP_ID, GROUP_NAME, GROUP_CODE, PARENT_CODE) 
TABLESPACE TS_TBL_GROUP;

create sequence TBL_GROUP_SEQ start with 1 increment by 1 nomaxvalue;

/* 3. TBL_CSRLOG */
CREATE TABLE TBL_CSRLOG(
  ACTIVITYID    NUMBER        NOT NULL, 
  ACTIVITYDT    DATE, 
  ACTIVITYCODE  VARCHAR2 (10), 
  ACTIVITYNAME  VARCHAR2 (60),
  ACTIVITYTYPE  VARCHAR2 (20),
  ACCTNO  		VARCHAR2 (19),
  ACCTTYPE      VARCHAR2 (4), 
  ACCTID        VARCHAR2 (60), 
  STMTID        VARCHAR2 (60), 
  STMTDT        VARCHAR2 (10), 
  USERID        VARCHAR2 (60), 
  ROLE          VARCHAR2 (50),
  ROLE_CODE		VARCHAR2 (10),
  MODULE_NAME	VARCHAR2 (200),
  BRANCH_CODE	VARCHAR2 (30),
  IPADDR        VARCHAR2 (30), 
  STATUS        VARCHAR2 (15), 
  DATE1         DATE, 
  DATE2         DATE, 
  USERFIELD1    VARCHAR2 (255), 
  USERFIELD2    VARCHAR2 (255), 
  USERFIELD3    VARCHAR2 (255), 
  USERFIELD4    VARCHAR2 (255) 
  )
  TABLESPACE TS_TBL_CSRLOG; 
  
  
CREATE INDEX IX1_TBL_CSRLOG
ON TBL_CSRLOG (
  ACTIVITYDT ASC,
  ACTIVITYCODE ASC
)
  TABLESPACE TS_TBL_CSRLOG;
 
  
CREATE INDEX IX2_TBL_CSRLOG
ON TBL_CSRLOG (
  ACTIVITYDT ASC,
  ACCTTYPE   ASC,
  ACCTID     ASC
)
  TABLESPACE TS_TBL_CSRLOG;
 
  
CREATE INDEX IX3_TBL_CSRLOG
ON TBL_CSRLOG (
  ACTIVITYDT ASC,
  USERID     ASC
)
  TABLESPACE TS_TBL_CSRLOG;

create sequence TBL_CSRLOG_SEQ start with 1 increment by 1 nomaxvalue;

-- 4. TBL_PARAMETERS
  CREATE TABLE TBL_PARAMETERS (
    MER_NAME    VARCHAR2(32 BYTE), 
	PARAM_TYPE  VARCHAR2(10 BYTE), 
	PARAM_CODE  VARCHAR2(10 BYTE), 
	PARAM_NAME  VARCHAR2(30 BYTE), 
	PARAM_VALUE VARCHAR2(30 BYTE), 
	PARAM_1     VARCHAR2(30 BYTE), 
	PARAM_2     VARCHAR2(30 BYTE), 
	PARAM_3     VARCHAR2(30 BYTE), 
	PARAM_4     VARCHAR2(30 BYTE), 
	CREATED_BY  VARCHAR2(60 BYTE), 
	CREATED_DATE DATE
   )
  TABLESPACE TS_TBL_PARAMETERS ;
 
  CREATE INDEX IX1_TBL_PARAMETERS ON TBL_PARAMETERS (MER_NAME, PARAM_TYPE) 
  TABLESPACE TS_TBL_PARAMETERS ;

-- 5. TBL_GROUP_ACCOUNT
CREATE TABLE BGADMIN.TBL_GROUP_ACCOUNT (
  GROUP_ACCOUNT_ID NUMBER(19,0)  NOT NULL, 
  GROUP_CODE 		VARCHAR2 (50) NOT NULL, 
  ACCOUNT_NO 		VARCHAR2 (50) NOT NULL, 
  CREATED_BY 		VARCHAR2 (50), 
  CREATED_DATE 		TIMESTAMP (6),
  UPDATED_BY 		VARCHAR2 (50), 
  UPDATED_DATE 		TIMESTAMP (6), 
  CONSTRAINT PK_TS_TBL_GROUP_ACCOUNT PRIMARY KEY (GROUP_ACCOUNT_ID)
)
TABLESPACE TS_TBL_GROUP_ACCOUNT;

CREATE INDEX BGADMIN.IX1_TBL_GROUP_ACCOUNT ON BGADMIN.TBL_GROUP_ACCOUNT (GROUP_ACCOUNT_ID, ACCOUNT_NO) 
TABLESPACE TS_TBL_GROUP_ACCOUNT;

create sequence TBL_GROUP_ACCOUNT_SEQ start with 1 increment by 1 nomaxvalue;

--6. TBL_MODULE
create table TBL_MODULE
(
  MER_NAME     VARCHAR2(32),
  MODULE_CODE  VARCHAR2(10) NOT NULL UNIQUE,
  MODULE_NAME  VARCHAR2(200),
  CREATED_BY   VARCHAR2(60),
  CREATED_DATE DATE,
  MODULE_URI   VARCHAR2(200),
  ORDER_BY     VARCHAR2(10),
  PARENT       VARCHAR2(60)
)
tablespace TS_TBL_MODULE;
-- Create/Recreate indexes 
create index IX1_TBL_MODULE on TBL_MODULE (MER_NAME, MODULE_CODE)
  tablespace TS_TBL_MODULE ;

  
--7. TBL_PERMISSION
create table TBL_PERMISSION
(
  MER_NAME     VARCHAR2(32),
  GROUP_CODE   VARCHAR2(60),
  MODULE_CODE  VARCHAR2(10),
  READ_FLAG    VARCHAR2(2),
  UPDATE_FLAG  VARCHAR2(2),
  WRITE_FLAG   VARCHAR2(2),
  CREATED_BY   VARCHAR2(60),
  CREATED_DATE DATE
)
tablespace TS_TBL_PERMISSION ;
-- Create/Recreate indexes 
create index IX1_TBL_PERMISSION on TBL_PERMISSION (MER_NAME, GROUP_CODE, MODULE_CODE)
  tablespace TS_TBL_PERMISSION ;

  
/* Start Create Trigger */
create trigger TBL_USER_TRIGGER before insert on TBL_USER
for each row
    begin
      select TBL_USER_SEQ.nextval
      into :new.USER_ID from dual;
end;

create trigger TBL_GROUP_TRIGGER before insert on TBL_GROUP
for each row
    begin
      select TBL_GROUP_SEQ.nextval
      into :new.GROUP_ID from dual;
end;

create trigger TBL_GROUP_ACCOUNT_TRIGGER before insert on TBL_GROUP_ACCOUNT
for each row
    begin
      select TBL_GROUP_ACCOUNT_SEQ.nextval
      into :new.GROUP_ACCOUNT_ID from dual;
end;

create trigger TBL_CSRLOG_TRIGGER before insert on TBL_CSRLOG
for each row
    begin
      select TBL_CSRLOG_SEQ.nextval
      into :new.ACTIVITYID from dual;
end;

/* End Create Trigger */
  
/* Master Data */
/* user :pass a12345 */
insert into TBL_USER (email,full_name, password, group_code, CREATED_DATE) values ('admin@astro.com.my', 'Astro Admin', 'af8f9dffa5d420fbc249141645b962ee','ASTRO_ADMIN', sysdate);

/* group */
insert into tbl_group (group_code, group_name, parent_code) values ('ASTRO_ADMIN', 'ASTRO ADMIN', null);
commit;


/* MODULE */

insert into tbl_module (module_name, module_code, order_by) values ('Bill Management', 'BEQ000','1000');
insert into tbl_module (module_name, module_code, module_uri, parent, order_by) values ('Bill Download', 'BEQ001' , 'bill/listGroup','BEQ000','1001');

insert into tbl_module (module_name, module_code, order_by) values ('System Maintenance', 'SMT000', '2000');
insert into tbl_module (module_name, module_code, module_uri, parent, order_by) values ('Groups', 'SMT001','securityList/listGroup','SMT000','2001');
insert into tbl_module (module_name, module_code, module_uri, parent, order_by) values ('Modules', 'SMT002','securityList/listModule','SMT000','2002');
insert into tbl_module (module_name, module_code, module_uri, parent, order_by) values ('Access Rights', 'SMT003','securityList/accessRight','SMT000','2003');
insert into tbl_module (module_name, module_code, module_uri, parent, order_by) values ('Accounts', 'SMT004','account/listAccount','SMT000','2004');

insert into tbl_module (module_name, module_code, order_by) values ('User Management', 'CAM000','3000');
insert into tbl_module (module_name, module_code, module_uri, parent, order_by) values ('Search User', 'CAM001', 'account/listUser','CAM000','3001');
insert into tbl_module (module_name, module_code, module_uri, parent, order_by) values ('Update User', 'CAM003', 'account/resetUser','CAM000','3002');

insert into tbl_module (module_name, module_code, order_by) values ('Report', 'RPT000','4000');
insert into tbl_module (module_name, module_code, module_uri, parent, order_by) values ('Audit Log', 'RPT001','auditlog/listAuditLog','RPT000','4001');

insert into tbl_module (module_name, module_code, order_by) values ('System Setting', 'SSM000','5000');
insert into tbl_module (module_name, module_code, module_uri, parent, order_by) values ('Parameter Setting', 'SSM001', 'system/paramSetting','SSM000','5001');

insert into tbl_module (module_name, module_code, module_uri, order_by) values ('Change Password', 'PRF001' , 'changepwd', '6000');
commit;

/* TBL_PERMISSION */

insert into TBL_PERMISSION (MER_NAME,GROUP_CODE,MODULE_CODE,READ_FLAG,UPDATE_FLAG,WRITE_FLAG) values ('ASTRO','ASTRO_ADMIN','SMT000', 'Y','Y','Y');
insert into TBL_PERMISSION (MER_NAME,GROUP_CODE,MODULE_CODE,READ_FLAG,UPDATE_FLAG,WRITE_FLAG) values ('ASTRO','ASTRO_ADMIN','SMT001', 'Y','Y','Y');
insert into TBL_PERMISSION (MER_NAME,GROUP_CODE,MODULE_CODE,READ_FLAG,UPDATE_FLAG,WRITE_FLAG) values ('ASTRO','ASTRO_ADMIN','SMT002', 'Y','Y','Y');
insert into TBL_PERMISSION (MER_NAME,GROUP_CODE,MODULE_CODE,READ_FLAG,UPDATE_FLAG,WRITE_FLAG) values ('ASTRO','ASTRO_ADMIN','SMT003', 'Y','Y','Y');
insert into TBL_PERMISSION (MER_NAME,GROUP_CODE,MODULE_CODE,READ_FLAG,UPDATE_FLAG,WRITE_FLAG) values ('ASTRO','ASTRO_ADMIN','SMT004', 'Y','Y','Y');
insert into TBL_PERMISSION (MER_NAME,GROUP_CODE,MODULE_CODE,READ_FLAG,UPDATE_FLAG,WRITE_FLAG) values ('ASTRO','ASTRO_ADMIN','RPT000', 'Y','Y','Y');
insert into TBL_PERMISSION (MER_NAME,GROUP_CODE,MODULE_CODE,READ_FLAG,UPDATE_FLAG,WRITE_FLAG) values ('ASTRO','ASTRO_ADMIN','RPT001', 'Y','Y','Y');
insert into TBL_PERMISSION (MER_NAME,GROUP_CODE,MODULE_CODE,READ_FLAG,UPDATE_FLAG,WRITE_FLAG) values ('ASTRO','ASTRO_ADMIN','SSM000', 'Y','Y','Y');
insert into TBL_PERMISSION (MER_NAME,GROUP_CODE,MODULE_CODE,READ_FLAG,UPDATE_FLAG,WRITE_FLAG) values ('ASTRO','ASTRO_ADMIN','SSM001', 'Y','Y','Y');
insert into TBL_PERMISSION (MER_NAME,GROUP_CODE,MODULE_CODE,READ_FLAG,UPDATE_FLAG,WRITE_FLAG) values ('ASTRO','ASTRO_ADMIN','CAM000', 'Y','Y','Y');
insert into TBL_PERMISSION (MER_NAME,GROUP_CODE,MODULE_CODE,READ_FLAG,UPDATE_FLAG,WRITE_FLAG) values ('ASTRO','ASTRO_ADMIN','CAM001', 'Y','Y','Y');
insert into TBL_PERMISSION (MER_NAME,GROUP_CODE,MODULE_CODE,READ_FLAG,UPDATE_FLAG,WRITE_FLAG) values ('ASTRO','ASTRO_ADMIN','CAM003', 'Y','Y','Y');
insert into TBL_PERMISSION (MER_NAME,GROUP_CODE,MODULE_CODE,READ_FLAG,UPDATE_FLAG,WRITE_FLAG) values ('ASTRO','ASTRO_ADMIN','BEQ000', 'Y','Y','Y');
insert into TBL_PERMISSION (MER_NAME,GROUP_CODE,MODULE_CODE,READ_FLAG,UPDATE_FLAG,WRITE_FLAG) values ('ASTRO','ASTRO_ADMIN','BEQ001', 'Y','Y','Y');
insert into TBL_PERMISSION (MER_NAME,GROUP_CODE,MODULE_CODE,READ_FLAG,UPDATE_FLAG,WRITE_FLAG) values ('ASTRO','ASTRO_ADMIN','PRF001', 'Y','Y','Y');
commit;
